package com.wstuo.itsm.scheduled.service;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.PipedInputStream;
import java.io.StringWriter;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.wstuo.common.config.category.entity.EventCategory;
import com.wstuo.common.config.dictionary.entity.DataDictionaryItems;
import com.wstuo.common.scheduled.dto.ScheduleDTO;
import com.wstuo.common.scheduled.dto.ScheduleEventDTO;
import com.wstuo.common.scheduled.dto.SchedulePreviewDTO;
import com.wstuo.common.scheduled.dto.ScheduleQueryDTO;
import com.wstuo.common.scheduled.dto.ScheduleShowDataDTO;
import com.wstuo.common.scheduled.dto.ScheduleViewDTO;
import com.wstuo.common.scheduledTask.dto.PatrTypeDateDTO;
import com.wstuo.common.tools.dao.ICostDAO;
import com.wstuo.common.tools.dao.IEventTaskDAO;
import com.wstuo.common.tools.dto.CostDTO;
import com.wstuo.common.tools.dto.EventTaskQueryDTO;
import com.wstuo.common.tools.entity.Cost;
import com.wstuo.common.tools.entity.EventTask;
import com.wstuo.common.tools.util.GenerationXISCellCommentsUtil;
import com.wstuo.common.tools.util.StructuralStyleUtil;
import com.wstuo.itsm.request.entity.Request;
import com.wstuo.itsm.request.dao.IRequestDAO;
import com.wstuo.itsm.scheduled.utils.TimeRange;
import com.wstuo.common.file.csv.CSVWriter;
import com.wstuo.common.security.dao.IUserDAO;
import com.wstuo.common.security.entity.User;
import com.wstuo.common.security.utils.AppContext;
import com.wstuo.common.security.utils.LanguageContent;
import com.wstuo.common.util.StringUtils;
import com.wstuo.common.util.TimeUtils;

/**
 * Schedule Service Class
 * 
 * @author WSTUO
 * 
 */
public class ScheduleService implements IScheduleService {
	private static final Logger LOGGER = Logger.getLogger(ScheduleService.class);
	@Autowired
	private ICostDAO costDAO;
	@Autowired
	private IUserDAO userDAO;
	@Autowired
	private IEventTaskDAO eventTaskDAO;
	@Autowired
	private IRequestDAO requestDAO;
	@Autowired
	private AppContext appctx;
	private LanguageContent lc = LanguageContent.getInstance();
	private static final String SHEETNAME="人员行程主界面";

	/**
	 * 设置数据
	 * 
	 * @param data
	 * @param scheduleShowDataDTO
	 * @param scheduleDTO
	 */
	private void setScheduleShowData(List<ScheduleShowDataDTO> data, ScheduleShowDataDTO scheduleShowDataDTO, ScheduleDTO scheduleDTO) {
		if (data != null && data.size() > 0) {
			// 当前技术员是否已经存在于数据集合中，如果没有则直接添加，有的话去判断是否存在日期冲突
			if (isExistTechnician(scheduleShowDataDTO.getLoginName(), data)) {
				dateConflict(data, scheduleShowDataDTO, scheduleDTO);
			} else {
				List<ScheduleDTO> scheduleDTOs = new ArrayList<ScheduleDTO>();
				scheduleDTOs.add(scheduleDTO);
				scheduleShowDataDTO.setData(scheduleDTOs);
				data.add(scheduleShowDataDTO);
			}

		} else if(data != null && data.size() == 0){// 如果当前data为空，则直接添加即可
			List<ScheduleDTO> scheduleDTOs = new ArrayList<ScheduleDTO>();
			scheduleDTOs.add(scheduleDTO);
			scheduleShowDataDTO.setData(scheduleDTOs);
			data.add(scheduleShowDataDTO);
		}
	}

	/**
	 * 判断这个List中是否已存在指定的技术员
	 * 
	 * @param technician
	 * @param data
	 * @return boolean
	 */
	private boolean isExistTechnician(String technician, List<ScheduleShowDataDTO> data) {
		boolean result = false;
		for (ScheduleShowDataDTO dto : data) {
			if (technician != null && technician.equals(dto.getLoginName())) {
				result = true;
			}
		}
		return result;
	}

	/**
	 * 日期冲突的处理
	 * 
	 * @param data
	 * @param scheduleShowDataDTO
	 * @param scheduleDTO
	 */
	private void dateConflict(List<ScheduleShowDataDTO> data, ScheduleShowDataDTO scheduleShowDataDTO, ScheduleDTO scheduleDTO) {
		// 是否已载入已有的集合中
		boolean isload = false;
		boolean result = false;
		for (ScheduleShowDataDTO dto : data) {
			if (dto.getLoginName() != null && dto.getLoginName().equals(scheduleShowDataDTO.getLoginName())) {
				List<ScheduleDTO> scheduleDTOs = dto.getData();
				String category = "";
				for (ScheduleDTO scheduleDTO1 : scheduleDTOs) {
					if (TimeRange.beForSpecifiedTime(scheduleDTO.getStartTime(), scheduleDTO.getEndTime(), scheduleDTO1.getStartTime(), scheduleDTO1.getEndTime())) {
						result = true;
						break;
					} else {
						category = scheduleShowDataDTO.getCategory();
						result = false;
					}
				}
				if (!result) {
					isload = true;
					scheduleDTOs.add(scheduleDTO);
					dto.setData(scheduleDTOs);
					if(StringUtils.hasText(category)){
						String[] categorys = dto.getCategory().split(",");
						List<String> list =Arrays.asList(categorys);
						if(!list.contains(category)){
							dto.setCategory(dto.getCategory()+","+category);
						}
					}
					break;
				}
			}
		}
		if (!isload) {
			List<ScheduleDTO> sd = new ArrayList<ScheduleDTO>();
			sd.add(scheduleDTO);
			scheduleShowDataDTO.setData(sd);
			data.add(scheduleShowDataDTO);
		}
	}

	/**
	 * 获取数据的相关信息
	 * 
	 * @param enos
	 * @return Map
	 */
	private Map<String, ScheduleEventDTO> getScheduleEvent(Set<String> enos) {
		Map<String, ScheduleEventDTO> events = new HashMap<String, ScheduleEventDTO>();
		for (String eno : enos) {
			String[] vals=eno.split(",");
			
				Request request = requestDAO.findById(Long.parseLong(vals[1]));
				ScheduleEventDTO dto = new ScheduleEventDTO();
				if (request != null) {
					dto.setEventCode(request.getRequestCode());
					dto.setTitle(request.getEtitle());
					DataDictionaryItems status = request.getStatus();
					if (status != null) {
						dto.setStatus(status.getDname());
					}
					EventCategory category = request.getEventCategory();
					if (category != null)
						dto.setCategory(category.getEventName());
					events.put(vals[0].replace(".","_")+"_" + vals[1], dto);
				}
		}

		return events;

	}

	/**
	 * 获取行程显示数据
	 * 
	 * @param qdto
	 * @param sidx
	 * @param sord
	 * @return ScheduleViewDTO
	 */
	@Transactional
	public ScheduleViewDTO findScheduleShowData(ScheduleQueryDTO qdto, String sidx, String sord) {
		
		CostDTO dto = new CostDTO();
		dto.setStartTime(qdto.getStartTime());
		dto.setEndTime(qdto.getEndTime());
		dto.setUserId(qdto.getTechnicianNo());
		EventTaskQueryDTO eventTaskQueryDTO = new EventTaskQueryDTO();
		eventTaskQueryDTO.setStartTime(qdto.getStartTime());
		eventTaskQueryDTO.setEndTime(qdto.getEndTime());
		if (qdto.getTechnicianNo() != null && qdto.getTechnicianNo() != 0) {
			User u = userDAO.findById(qdto.getTechnicianNo());
			if(u!=null)
				eventTaskQueryDTO.setOwner(u.getLoginName());
		}
		// 最终返回前台显示的DTO
		List<ScheduleShowDataDTO> data = new ArrayList<ScheduleShowDataDTO>();
		Set<String> costEnos = new HashSet<String>();
		// 如果是项目人天、AMS人天、IT Tickets则查询Tickets
		if ("All".equals(qdto.getScheduleType()) || "Project".equals(qdto.getScheduleType()) || "Tickets".equals(qdto.getScheduleType()) || "It".equals(qdto.getScheduleType()) || "Cost".equals(qdto.getScheduleType())) {
			getTicket(dto, costEnos, data);
		}
		// 查询某一时间段的全部技术员任务
		if ("All".equals(qdto.getScheduleType()) || "Task".equals(qdto.getScheduleType())) {// 如果查询类型是全部及计划人天
			getTask(eventTaskQueryDTO, costEnos, data);
		}
		
		ScheduleViewDTO scheduleViewDTO = new ScheduleViewDTO();
		scheduleViewDTO.setScheduleShowDataDTO(data);
		scheduleViewDTO.setEvents(getScheduleEvent(costEnos));
		scheduleViewDTO.setMaximumTime(TimeUtils.format(qdto.getEndTime(), TimeUtils.DATE_PATTERN));
		scheduleViewDTO.setMinimumTime(TimeUtils.format(qdto.getStartTime(), TimeUtils.DATE_PATTERN));
		return scheduleViewDTO;
	}

	/**
	 * 获取Tickets
	 * 
	 * @param qdto
	 * @param dto
	 * @param costEnos
	 * @param data
	 */
	private void getTicket(CostDTO dto, Set<String> costEnos, List<ScheduleShowDataDTO> data) {
		// 查询所有的进展及成本
		List<Cost> costs = costDAO.findScheduleCost(dto, null, "users", "desc");
		// 获取所有技术员
		if (costs != null && costs.size() > 0) {
			for (Cost cost : costs) {
				costEnos.add(cost.getEventType()+","+cost.getEno());
				ScheduleShowDataDTO dataDTO = new ScheduleShowDataDTO();
				// 行程前台数据显示DTO
				ScheduleDTO scheduleDTO = new ScheduleDTO();
				scheduleDTO.setStartTime(cost.getStartTime());
				scheduleDTO.setEndTime(cost.getEndTime());
				scheduleDTO.setEno(cost.getEno());
				scheduleDTO.setHang(cost.getType());// 挂起
				scheduleDTO.setActualTime(cost.getActualTime());
				scheduleDTO.setEventType(cost.getEventType());
				if (cost.getStatus() == 1)
					scheduleDTO.setStatus(lc.getContent("title.processing"));
				else if (cost.getStatus() == 2)
					scheduleDTO.setStatus(lc.getContent("lable.request.Completed"));
				EventCategory eventCategory=null;
				// 设置类型
				if(cost.getEventType().equals("itsm.request")){
					Request reqeust = requestDAO.findById(cost.getEno());
					if (reqeust != null) {
						eventCategory = reqeust.getEventCategory();
					}
				}
				if(eventCategory!=null){
					String categoryName = "";
					if (eventCategory != null) {
						categoryName = eventCategory.getEventName();
					}
					dataDTO.setCategory(categoryName);
					dataDTO.setType("Tickets");
					scheduleDTO.setType("Tickets");
				}
				User user = cost.getUsers();
				if (user != null) {
					dataDTO.setUserId(user.getUserId());
					dataDTO.setLoginName(user.getLoginName());
					dataDTO.setTechnician(user.getFullName());
				}
				setScheduleShowData(data, dataDTO, scheduleDTO);
			}
		}
	}

	/**
	 * 获取Task
	 * 
	 * @param taskQueryDTO
	 * @param data
	 */
	private void getTask(EventTaskQueryDTO queryDTO, Set<String> costEnos, List<ScheduleShowDataDTO> data) {
		queryDTO.setSidx("owner");
		queryDTO.setSord("desc");
		List<EventTask> eventTasks =  eventTaskDAO.findScheduleEventTask(queryDTO);
		if (eventTasks != null && eventTasks.size() > 0) {
			for (EventTask task : eventTasks) {
				ScheduleShowDataDTO dataDTO = new ScheduleShowDataDTO();
				costEnos.add(task.getEventType()+","+ task.getEno() );
				// 行程前台数据显示DTO
				ScheduleDTO scheduleDTO = new ScheduleDTO();
				scheduleDTO.setStartTime(task.getStartTime());
				scheduleDTO.setEndTime(task.getEndTime());
				scheduleDTO.setTaskId(task.getTaskId());
				scheduleDTO.setTitle(task.getTitle());
				scheduleDTO.setType("Task");
				scheduleDTO.setEno(task.getEno());
				scheduleDTO.setEventType(task.getEventType());
				scheduleDTO.setIntroduction(task.getIntroduction());
				if (task.getTaskStatus() == 0)
					scheduleDTO.setStatus(lc.getContent("title.newCreate"));
				if (task.getTaskStatus() == 1)
					scheduleDTO.setStatus(lc.getContent("title.processing"));
				if (task.getTaskStatus() == 2)
					scheduleDTO.setStatus(lc.getContent("lable.request.Completed"));
				scheduleDTO.setAllDay(task.getAllDay());
				scheduleDTO.setLocation(task.getLocation());

				User user = userDAO.findUniqueBy("loginName", task.getOwner().getLoginName());
				if (user != null) {
					dataDTO.setUserId(user.getUserId());
					dataDTO.setLoginName(user.getLoginName());
					dataDTO.setTechnician(user.getFullName());
				}
				dataDTO.setType("Task");

				setScheduleShowData(data, dataDTO, scheduleDTO);
			}
		}
	}

	/**
	 * 得到当前行程视图显示的开始及结束时间
	 * 
	 * @param data
	 * @return
	 */
	@SuppressWarnings("unused")
	private String[] findMaximumTime(List<ScheduleShowDataDTO> data) {
		String[] str = new String[]{null, null};
		Date maximumtime = null;
		Date minimumtime = null;
		boolean falg = true;
		for (ScheduleShowDataDTO dto : data) {
			if (dto.getData() != null) {
				for (ScheduleDTO sdto : dto.getData()) {
					if (falg) {
						maximumtime = sdto.getEndTime();
						minimumtime = sdto.getStartTime();
						falg = false;
					}
					if (sdto.getEndTime().getTime() > maximumtime.getTime()) {
						maximumtime = sdto.getEndTime();
					}
					if (sdto.getStartTime().getTime() < minimumtime.getTime()) {
						minimumtime = sdto.getStartTime();
					}
				}
			}
		}
		if (maximumtime != null)
			str[0] = TimeUtils.format(maximumtime, TimeUtils.DATE_PATTERN);
		if (minimumtime != null)
			str[1] = TimeUtils.format(minimumtime, TimeUtils.DATE_PATTERN);
		return str;
	}
	@Transactional
	private static Long getPlanDay(ScheduleViewDTO scheduleViewDTO, String userName) {
		Long total = 0L;
		for (ScheduleShowDataDTO dto : scheduleViewDTO.getScheduleShowDataDTO()) {
			// 得到当前顾问下的全部时间
			if (userName.equals(dto.getTechnician()) && dto.getData() != null) {
				for (ScheduleDTO sdto : dto.getData()) {
					total = total + TimeUtils.periodDays(sdto.getStartTime(), sdto.getEndTime());
				}
			}
		}
		return total;
	}

	/**
	 * 当前行程视图导出
	 * 
	 * @param qdto
	 * @param sidx
	 * @param sord
	 * @return InputStream
	 */
	@Transactional
	public InputStream exportScheduleData(ScheduleQueryDTO qdto, String sidx, String sord) {
		ScheduleViewDTO scheduleViewDTO = findScheduleShowData(qdto, sidx, sord);
		StringWriter sw = new StringWriter();
		CSVWriter csvw = new CSVWriter(sw);
		ByteArrayInputStream stream =null;
		// 构造标题
		List<String[]> colsData = new ArrayList<String[]>();
		colsData = exportScheduleDataConstructTitle();
		// 构造内容
		colsData = exportScheduleDataConstructContent(qdto, scheduleViewDTO);
		csvw.writeAll(colsData);
		byte[] bs = null;
		try {
			bs = sw.getBuffer().toString().getBytes("GBK");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		 stream = new ByteArrayInputStream(bs);
		return stream;
	}
	/**
	 * 视图导出标题构造
	 * 
	 * @return List<String[]>
	 */
	@Transactional
	private List<String[]> exportScheduleDataConstructTitle() {
		List<String[]> colsData = new ArrayList<String[]>();

		colsData.add(new String[]{"ID", lc.getContent("lable.Consultant.Name"), lc.getContent("lable.Responsible.module"), lc.getContent("lable.Query.start.date"), lc.getContent("lable.Query.end.date"), lc.getContent("lable.Query.available.during.weekdays"),
				lc.getContent("lable.Has.plans.workday.during.inquiry"), lc.getContent("common.id"), lc.getContent("common.title"), "Tickets" + lc.getContent("common.status"), lc.getContent("lable.Stroke.Types"), lc.getContent("label.sla.slaStartTime"), lc.getContent("label.sla.slaEndTime"),
				lc.getContent("lable.Stroke.status"), "Tickets" + lc.getContent("lable.Plans.workday"), lc.getContent("lable.Complete.workday"), lc.getContent("label.sla.completeRate")});
		return colsData;
	}
	/**
	 * 视图导出标题内容
	 * 
	 * @return List<String[]>
	 */
	@Transactional
	private List<String[]> exportScheduleDataConstructContent(ScheduleQueryDTO qdto, ScheduleViewDTO scheduleViewDTO) {
		List<String[]> colsData = new ArrayList<String[]>();
		DecimalFormat myformat = new DecimalFormat("#####0.00");
		for (ScheduleShowDataDTO dto : scheduleViewDTO.getScheduleShowDataDTO()) {
			ScheduleEventDTO event = new ScheduleEventDTO();
			if (dto.getData() != null) {
				for (ScheduleDTO sdto : dto.getData()) {
					event = scheduleViewDTO.getEvents().get("eno_" + sdto.getEno());
					if (event == null)
						event = new ScheduleEventDTO();

					String userId = "";
					if (dto.getUserId() != null)
						userId = dto.getUserId().toString();

					// 查询期间内可用工作日
					Long queryWorkTime = TimeUtils.periodDays(qdto.getStartTime(), qdto.getEndTime());

					// Tickets计划工作日
					Long planWorkTime = TimeUtils.periodDays(sdto.getStartTime(), sdto.getEndTime());
					// 完成工作日
					Integer complete = 0;
					if (!"Task".equals(sdto.getType())) {
						complete = sdto.getActualTime().intValue() / (60 * 8);
					}
					// 完成率
					String completes = "0";
					if (planWorkTime != 0) {
						completes = myformat.format((complete.doubleValue() / planWorkTime.doubleValue()) * 100);
					}
					colsData.add(new String[]{userId,// ID
							dto.getTechnician(),// 顾问姓名
							dto.getModule(),// 负责模块
							TimeUtils.format(qdto.getStartTime(), TimeUtils.DATETIME_PATTERN),// 查询起始日期
							TimeUtils.format(qdto.getEndTime(), TimeUtils.DATETIME_PATTERN),// 查询结束日期

							queryWorkTime.toString(),// 查询期间内可用工作日
							getPlanDay(scheduleViewDTO, dto.getTechnician()).toString(),// 查询期间内已计划工作日
							event.getEventCode(),// 编号
							event.getTitle(),// 标题
							event.getStatus(),// Tickets状态

							sdto.getType(),// 行程类型
							TimeUtils.format(sdto.getStartTime(), TimeUtils.DATETIME_PATTERN),// 开始时间
							TimeUtils.format(sdto.getEndTime(), TimeUtils.DATETIME_PATTERN),// 结束时间
							sdto.getStatus(),// 行程状态
							planWorkTime.toString(),// Tickets计划工作日

							complete.toString(),// 完成工作日
							completes + "%"// 完成率
					});
				}
			}
		}
		return colsData;
	}
	/**
	 * 预览导出效果
	 * 
	 * @param qdto
	 * @param sidx
	 * @param sord
	 * @return List<SchedulePreviewDTO>
	 */
	@Transactional
	public List<SchedulePreviewDTO> exportSchedulePreview(ScheduleQueryDTO qdto, String sidx, String sord) {
		List<SchedulePreviewDTO> sp = new ArrayList<SchedulePreviewDTO>();
		DecimalFormat myformat = new DecimalFormat("#####0.00");
		ScheduleViewDTO scheduleViewDTO = findScheduleShowData(qdto, sidx, sord);
		for (ScheduleShowDataDTO dto : scheduleViewDTO.getScheduleShowDataDTO()) {
			ScheduleEventDTO event = new ScheduleEventDTO();
			if (dto.getData() != null) {
				for (ScheduleDTO sdto : dto.getData()) {
					event = scheduleViewDTO.getEvents().get("eno_" + sdto.getEno());
					if (event == null)
						event = new ScheduleEventDTO();

					String userId = "";
					if (dto.getUserId() != null)
						userId = dto.getUserId().toString();

					// 查询期间内可用工作日
					Long queryWorkTime = TimeUtils.periodDays(qdto.getStartTime(), qdto.getEndTime());

					// Tickets计划工作日
					Long planWorkTime = TimeUtils.periodDays(sdto.getStartTime(), sdto.getEndTime());
					// 完成工作日
					Integer complete = 0;
					if (!"Task".equals(sdto.getType())) {
						complete = sdto.getActualTime().intValue() / (60 * 8);
					}
					// 完成率
					String completes = "0";
					if (planWorkTime != 0) {
						completes = myformat.format((complete.doubleValue() / planWorkTime.doubleValue()) * 100);
					}

					SchedulePreviewDTO spDTO = new SchedulePreviewDTO();
					spDTO.setId(userId);// ID
					spDTO.setUserName(dto.getTechnician());// 顾问姓名
					spDTO.setModule(dto.getModule());// 负责模块
					spDTO.setQueryStartTime(TimeUtils.format(qdto.getStartTime(), TimeUtils.DATETIME_PATTERN));// 查询起始日期
					spDTO.setQueryEndTime(TimeUtils.format(qdto.getEndTime(), TimeUtils.DATETIME_PATTERN));// 查询结束日期
					spDTO.setQueryUseDay(queryWorkTime.toString());// 查询期间内可用工作日
					spDTO.setQueryPlanDay(getPlanDay(scheduleViewDTO, dto.getTechnician()).toString());// 查询期间内已计划工作日
					spDTO.setTicketNo(event.getEventCode());// 编号
					spDTO.setTicketTitle(event.getTitle());// 标题
					spDTO.setTicketStatus(event.getStatus());// Tickets状态
					spDTO.setScheduleType(sdto.getType());// 行程类型
					spDTO.setStartTime(TimeUtils.format(sdto.getStartTime(), TimeUtils.DATETIME_PATTERN));// 开始时间
					spDTO.setEndTime(TimeUtils.format(sdto.getEndTime(), TimeUtils.DATETIME_PATTERN));// 结束时间
					spDTO.setScheduleStatus(sdto.getStatus());// 行程状态
					spDTO.setTicketPlanWorkingDay(planWorkTime.toString());// Tickets计划工作日
					spDTO.setCompleteWorkingDay(complete.toString());// 完成工作日
					spDTO.setCompleteRates(completes + "%");// 完成率
					sp.add(spDTO);
				}
			}
		}

		return sp;
	}

	/**
	 * 人员行程主界面导出
	 * 
	 * @param qdto
	 * @param sidx
	 * @param sord
	 * @return InputStream
	 */
	@Transactional
	public InputStream exportScheduleView(ScheduleQueryDTO qdto, String sidx, String sord) {
		// 查询数据
		ScheduleViewDTO scheduleViewDTO = findScheduleShowData(qdto, sidx, sord);
		String sheetName = lc.getContent("label.schedule.scheduleMain");// 表名
		if("".equals(sheetName)){
			sheetName=SHEETNAME;
		}
		// 根据最小时间和最大时间生成时间列
		List<Date> dates = TimeRange.getTimeBetweenDays(scheduleViewDTO.getMinimumTime(), scheduleViewDTO.getMaximumTime());
		// 构造标题
		String[] colNames = exportScheduleViewConstructTitle(dates);

		// 样式
		PipedInputStream pis = null;
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);
		HSSFPalette palette = wb.getCustomPalette();
		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.WHITE.index);

		HSSFCellStyle cellstyle = wb.createCellStyle();
		cellstyle.setFillBackgroundColor(HSSFCellStyle.ALT_BARS);

		HSSFRow rowHead = sheet.createRow((short) 0);
		sheet.createFreezePane(0, 1);
		// 生成标题
		for (int i = 0; i < colNames.length; i++) {
			if (colNames[i] != null && TimeUtils.weekend(colNames[i])) {
				GenerationXISCellCommentsUtil.createCell(rowHead, StructuralStyleUtil.getweekendStyle(wb, palette), (short) i, colNames[i], null);
			} else {
				GenerationXISCellCommentsUtil.createCell(rowHead, cellstyle, (short) i, colNames[i], null);
			}
		}
		// 生成内容
		exportScheduleViewContent(scheduleViewDTO, dates, wb, palette, font, sheet, cellstyle);
		try {
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			wb.write(os);
			return new ByteArrayInputStream(os.toByteArray());

		} catch (Exception e) {
			LOGGER.error(e);
		}
		return pis;

	}
	/**
	 * 生成内容数据构造
	 * 
	 * @param scheduleViewDTO
	 * @param dates
	 * @param wb
	 * @param palette
	 * @param font
	 * @param sheet
	 * @param cellstyle
	 */
	@Transactional
	private void exportScheduleViewContent(ScheduleViewDTO scheduleViewDTO, List<Date> dates, HSSFWorkbook wb, HSSFPalette palette, HSSFFont font, HSSFSheet sheet, HSSFCellStyle cellstyle) {
		PatrTypeDateDTO patrTypeDateDTO = exportScheduleViewPatrTypeDate(scheduleViewDTO, dates);
		// 数据构造
		List<String[]> patrData = patrTypeDateDTO.getPatrData();
		List<String[]> types = patrTypeDateDTO.getTypes();
		List<String[]> data = patrTypeDateDTO.getData();// 数据
		// 生成内容
		for (int j = 0; j < data.size(); j++) {
			HSSFRow row = sheet.createRow((short) j + 1);
			String[] rowData = data.get(j);
			String[] p = patrData.get(j);
			String[] t = types.get(j);
			for (int i = 0; i < rowData.length; i++) {
				if (StringUtils.hasText(p[i])) {
					// 批注，创建绘图对象
					HSSFPatriarch patr = sheet.createDrawingPatriarch();
					// 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
					HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 7, 10));
					// 输入批注信息
					comment.setString(new HSSFRichTextString(p[i]));
					// 添加作者,选中B5单元格,看状态栏
					comment.setAuthor("system");
					if ("Project".equals(t[i]))
						GenerationXISCellCommentsUtil.createCell(row, StructuralStyleUtil.getPstyle(wb, palette, font), (short) i, rowData[i], comment);
					else if ("It".equals(t[i]))
						GenerationXISCellCommentsUtil.createCell(row, StructuralStyleUtil.getItstyle(wb, palette, font), (short) i, rowData[i], comment);
					else if ("Tickets".equals(t[i]))
						GenerationXISCellCommentsUtil.createCell(row, StructuralStyleUtil.getAstyle(wb, palette, font), (short) i, rowData[i], comment);
					else if ("Task".equals(t[i])) {
						GenerationXISCellCommentsUtil.createCell(row, StructuralStyleUtil.getTstyle(wb, palette, font), (short) i, rowData[i], comment);
					} else
						GenerationXISCellCommentsUtil.createCell(row, cellstyle, (short) i, rowData[i], comment);
				} else {
					GenerationXISCellCommentsUtil.createCell(row, cellstyle, (short) i, rowData[i], null);
				}
			}
		}
	}

	/**
	 * 人员行程主界面构造标题
	 * 
	 * @param scheduleViewDTO
	 * @param dates
	 * @return
	 */
	@Transactional
	private String[] exportScheduleViewConstructTitle(List<Date> dates) {
		int count = 1;
		/** 构造标题 start */
		LanguageContent lc = LanguageContent.getInstance();
		String lang = appctx.getCurrentLanguage();
		String[] colNames = new String[dates.size() + count];
		//colNames[0] = lc.getContent("common.category", lang);
		colNames[0] = lc.getContent("label.request.theTechnician", lang);

		for (int i = 0; dates.size() > i; i++) {
			colNames[i + count] = TimeUtils.format(dates.get(i), TimeUtils.DATE_PATTERN);
		}
		/** 构造标题 end */
		return colNames;
	}
	/**
	 * 人员行程主界面构造内容
	 * 
	 * @param scheduleViewDTO
	 * @param dates
	 * @return
	 */
	@Transactional
	private PatrTypeDateDTO exportScheduleViewPatrTypeDate(ScheduleViewDTO scheduleViewDTO, List<Date> dates) {
		int count = 1;
		PatrTypeDateDTO dto = new PatrTypeDateDTO();
		Integer daysize = dates.size();
		/** 构造行数据 start */
		// 批注信息
		List<String[]> patrData = new ArrayList<String[]>();
		// 类型
		List<String[]> types = new ArrayList<String[]>();
		List<String[]> data = new ArrayList<String[]>();// 数据
		if (scheduleViewDTO != null && scheduleViewDTO.getScheduleShowDataDTO() != null) {
			for (ScheduleShowDataDTO showDto : scheduleViewDTO.getScheduleShowDataDTO()) {
				String[] rows = new String[daysize + count];// 显示信息
				String[] pd = new String[daysize + count];// 备注
				String[] type = new String[daysize + count];// 类型
				/*if (StringUtils.hasText(showDto.getCategory()) && !"null".equals(showDto.getCategory())) {
					rows[0] = showDto.getCategory();
				} else {
					rows[0] = "";
				}*/
				rows[0] = showDto.getTechnician();
				// 判断是否全是任务并且时间段不在行程时间内
				/*if (TimeRange.scheduleIsAllTask(showDto.getData(), scheduleViewDTO.getMinimumTime(), scheduleViewDTO.getMaximumTime())) {
					
				}*/
				for (int i = 0; dates.size() > i; i++) {
					String[] result = dateContain(showDto.getData(), scheduleViewDTO.getEvents(), dates.get(i));
					rows[i + count] = result[0];
					pd[i + count] = result[1];
					type[i + count] = result[2];
				}
				data.add(rows);
				patrData.add(pd);
				types.add(type);

			}
		}
		/** 构造行数据 end */
		dto.setData(data);
		dto.setPatrData(patrData);
		dto.setTypes(types);
		return dto;
	}
	@Transactional
	private String[] dateContain(List<ScheduleDTO> data, Map<String, ScheduleEventDTO> events, Date specifyDate) {
		StringBuilder result = new StringBuilder();
		StringBuilder patr = new StringBuilder();
		StringBuilder type = new StringBuilder();
		for (ScheduleDTO dto : data) {
			dto.setStartTime(TimeUtils.dateFormat(dto.getStartTime(), TimeUtils.DATE_PATTERN));
			dto.setEndTime(TimeUtils.dateFormat(dto.getEndTime(), TimeUtils.DATE_PATTERN));
			// 判断是否在指定的时间段内
			if (dto.getStartTime().getTime() <= specifyDate.getTime() && dto.getEndTime().getTime() >= specifyDate.getTime()) {

				// 判断否是开始时间
				if (TimeUtils.format(dto.getStartTime(), TimeUtils.DATE_PATTERN).equals(TimeUtils.format(specifyDate, TimeUtils.DATE_PATTERN))) {
					if (!"".equals(result.toString()))
						result.append("/Start");
					else
						result.append("Start");

				}
				// 判断否是结束时间
				if (TimeUtils.format(dto.getEndTime(), TimeUtils.DATE_PATTERN).equals(TimeUtils.format(specifyDate, TimeUtils.DATE_PATTERN))) {
					if (!"".equals(result.toString()))
						result.append("/End");
					else
						result.append("End");
				}
				// 判断行程类型
				if ("Project".equals(dto.getType())) {
					if (!"".equals(result.toString()))
						result.append("/P");
					else
						result.append("P");
				}
				if ("It".equals(dto.getType())) {
					if (!"".equals(result.toString()))
						result.append("/IT");
					else
						result.append("IT");
				}
				if ("Tickets".equals(dto.getType())) {
					if (!"".equals(result.toString()))
						result.append("/C");
					else
						result.append("C");
				}

				// 批注信息
				ScheduleEventDTO se = events.get("eno_" + dto.getEno());
				if (se != null) {
					patr.append("Ticket No:" + se.getEventCode());
					patr.append("\n" + lc.getContent("common.title") + ":" + se.getTitle());
					patr.append("\n" + lc.getContent("common.state") + ":" + se.getStatus());
				}
				patr.append("\n" + lc.getContent("label.sla.slaStartTime") + ":" + TimeUtils.format(dto.getStartTime(), TimeUtils.DATETIME_PATTERN));
				patr.append("\n" + lc.getContent("label.sla.slaEndTime") + ":" + TimeUtils.format(dto.getEndTime(), TimeUtils.DATETIME_PATTERN));
				type.append(dto.getType());
				if ("Task".equals(dto.getType())) {
					// 判断指定时间是否在开始与结束时间之前
					/*if (TimeUtils.dateFormat(specifyDate, TimeUtils.DATE_PATTERN).getTime() >= TimeUtils.dateFormat(new Date(), TimeUtils.DATE_PATTERN).getTime()) {
						
					} else {
						se = null;
						result = new StringBuilder();
						type = new StringBuilder();
						patr = new StringBuilder();
					}*/
					if (!"".equals(result.toString()))
						result.append("/T");
					else
						result.append("T");

				}
				break;
			}
		}

		String[] strs = new String[]{result.toString(), patr.toString(), type.toString()};
		return strs;
	}

}
